if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vw_PartiesInfo]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vw_PartiesInfo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VW_Vouchers]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VW_Vouchers]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE VIEW dbo.VW_Vouchers
AS
SELECT     dbo.VoucherDetails.VoucherID, dbo.Voucher.VoucherDate, dbo.Voucher.VoucherNo, dbo.Voucher.SessionID, dbo.Voucher.BusinessUnitID, 
                      dbo.Voucher.Description AS MasterDescription, dbo.Voucher.DailyBalancesID, dbo.Voucher.ReadOnly, dbo.VoucherDetails.VoucherDetailID, 
                      dbo.VoucherDetails.AccountNo, dbo.Accounts.AccountTitle, dbo.SessionInfo.SessionTitle, dbo.SessionInfo.CurrentSession, dbo.gen_BusinessUnitsInfo.BusinessUnitTitle, 
                      dbo.VoucherDetails.Description, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE - 1 * dbo.VoucherDetails.Amount END AS Amount, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE 0 END AS Debit, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Cr') 
                      THEN dbo.VoucherDetails.Amount ELSE 0 END AS Credit, dbo.VoucherDetails.DrOrCr, dbo.Accounts.ParentAccount, dbo.Accounts.AccountType                      
FROM         dbo.Accounts RIGHT OUTER JOIN
                      dbo.VoucherDetails ON dbo.Accounts.AccountNo = dbo.VoucherDetails.AccountNo LEFT OUTER JOIN
                      dbo.Voucher ON dbo.VoucherDetails.VoucherID = dbo.Voucher.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID LEFT OUTER JOIN
                      dbo.gen_BusinessUnitsInfo ON dbo.Voucher.BusinessUnitID = dbo.gen_BusinessUnitsInfo.BusinessUnitID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE VIEW dbo.vw_PartiesInfo
AS
SELECT     dbo.gen_AreaInfo.RegionID, dbo.gen_RegionInfo.RegionTitle, dbo.gen_PartiesInfo.AreaID, dbo.gen_AreaInfo.AreaTitle, dbo.gen_PartiesInfo.SectorID, 
                      dbo.gen_SectorInfo.SectorTitle, dbo.gen_PartiesInfo.PartyTypeID, dbo.gen_PartyTypesInfo.PartyTypeTitle, dbo.gen_PartiesInfo.PartyID, 
                      dbo.gen_PartiesInfo.PartyCode, dbo.gen_PartiesInfo.PartyTitle, dbo.gen_PartiesInfo.AccountNo, ISNULL(Opening.Opening, 0) AS Opening, 
                      dbo.gen_PartiesInfo.BusinessType, dbo.gen_PartiesInfo.Address, dbo.gen_PartiesInfo.City, dbo.gen_PartiesInfo.HomePhone, dbo.gen_PartiesInfo.MobilePhone, 
                      dbo.gen_PartiesInfo.NICNO, dbo.gen_PartiesInfo.HireDate, dbo.gen_PartiesInfo.Email, dbo.gen_PartiesInfo.Description, dbo.gen_PartiesInfo.Discontinue, 
                      dbo.gen_PartiesInfo.ShowIn
FROM         dbo.gen_AreaInfo LEFT OUTER JOIN
                      dbo.gen_RegionInfo ON dbo.gen_AreaInfo.RegionID = dbo.gen_RegionInfo.RegionID RIGHT OUTER JOIN
                      dbo.gen_SectorInfo RIGHT OUTER JOIN
                      dbo.gen_PartiesInfo LEFT OUTER JOIN
                          (SELECT     AccountNo, SUM(Opening) AS Opening
                            FROM          (SELECT     SessionBalances.AccountNo, SUM(ISNULL(SessionBalances.OpeningBal, 0))  
                                                                            AS Opening
                                                    FROM          SessionBalances RIGHT OUTER JOIN
                                                                           gen_PartiesInfo ON SessionBalances.AccountNo = gen_PartiesInfo.AccountNo
						    WHERE SessionID IN (SELECT SessionID FROM dbo.SessionInfo WHERE dbo.SessionInfo.CurrentSession=1)	
                                                    GROUP BY SessionBalances.AccountNo
) TempTable
                            GROUP BY AccountNo) Opening ON dbo.gen_PartiesInfo.AccountNo = Opening.AccountNo LEFT OUTER JOIN
                      dbo.gen_PartyTypesInfo ON dbo.gen_PartiesInfo.PartyTypeID = dbo.gen_PartyTypesInfo.PartyTypeID ON 
                      dbo.gen_SectorInfo.SectorID = dbo.gen_PartiesInfo.SectorID ON dbo.gen_AreaInfo.AreaID = dbo.gen_PartiesInfo.AreaID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO